package com.mygs.trackppt.utils;

import com.mygs.trackppt.pojo.OOCChartData;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xslf.usermodel.XMLSlideShow;
import org.apache.poi.xslf.usermodel.XSLFChart;
import org.apache.poi.xslf.usermodel.XSLFGraphicFrame;
import org.apache.poi.xslf.usermodel.XSLFShape;
import org.apache.poi.xslf.usermodel.XSLFSlide;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTBarChart;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTBarSer;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTChart;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTLineChart;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTLineSer;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumData;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumVal;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTPlotArea;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTStrData;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTStrVal;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTUnsignedInt;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTValAx;
import org.springframework.util.CollectionUtils;
import org.springframework.util.ResourceUtils;

import java.io.FileOutputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

@Slf4j
public class SummaryChartPptUtil {
    public static boolean generateOOCPPTChart(String templateFilePath, String outputFilePath,
        List<OOCChartData> chartDataList) {
        try {
            // 从资源路径加载PPT模板文件
            InputStream fis = ResourceUtils.getURL(templateFilePath).openStream();
            // 创建XMLSlideShow对象，表示一个PPT演示文稿
            XMLSlideShow ppt = new XMLSlideShow(fis);
            // 关闭输入流
            fis.close();

            // 调用makePPT方法生成PPT内容
            makeOOCPPT(ppt, chartDataList);

            // 修改完 ppt 后保存
            try (FileOutputStream out = new FileOutputStream(outputFilePath)) {
                // 将修改后的PPT写入输出流
                ppt.write(out);
                // 刷新输出流
                out.flush();
            }

            log.info("PPT生成成功！文件路径：{}", outputFilePath);
            return true;
        } catch (Exception e) {
            log.error("PPT生成失败", e);
            return false;
        }
    }

    private static void makeOOCPPT(XMLSlideShow ppt, List<OOCChartData> chartDataList) throws Exception {
        XSLFSlide slide = ppt.getSlides().get(0);
        // XSLFChart chart = null;
        List<XSLFChart> chartList = new ArrayList<>();
        for (XSLFShape shape : slide.getShapes()) {
            if (shape instanceof XSLFGraphicFrame) {
                XSLFGraphicFrame graphicFrame = (XSLFGraphicFrame) shape;
                // 获取图形框架中的图表对象
                XSLFChart chart = graphicFrame.getChart();
                if (Objects.nonNull(chart)) {
                    chartList.add(chart);
                }
            }
        }
        if (CollectionUtils.isEmpty(chartList)) {
            return;
        }
        fillOOCChartToPPT(chartList, chartDataList);
    }

    private static void fillOOCChartToPPT(List<XSLFChart> chartList, List<OOCChartData> oocChartDataList)
    throws Exception {
        XSSFWorkbook workbook = chartList.get(0).getWorkbook();
        log.info("获取到工作簿和工作表");

        for (int i = 0; i < chartList.size(); i++) {
            XSSFSheet sheet = workbook.getSheetAt(i);
            resetSheetData(sheet, oocChartDataList.get(i));

            XSLFChart chart = chartList.get(i);

            // 更新 chart XML 中的数据缓存
            CTChart ctChart = chart.getCTChart();
            CTPlotArea plotArea = ctChart.getPlotArea();

            CTValAx ctValAx = plotArea.getValAxList().get(1);
            if (!ctValAx.isSetNumFmt()) {
                ctValAx.addNewNumFmt();
            }
            ctValAx.getNumFmt().setFormatCode("0.00%");
            ctValAx.getNumFmt().setSourceLinked(false);

            // (1) 更新柱状图数据
            for (CTBarChart barChart : plotArea.getBarChartList()) {
                for (CTBarSer ser : barChart.getSerList()) {
                    updateSeriesData(ser, sheet);
                }
            }

            // (2) 更新折线图数据
            for (CTLineChart lineChart : plotArea.getLineChartList()) {
                for (CTLineSer ser : lineChart.getSerList()) {
                    updateSeriesData(ser, sheet);
                }
            }
        }

    }

    private static void updateSeriesData(CTBarSer ser, Sheet sheet) {
        // 填充X轴
        if (ser.isSetCat() && ser.getCat().isSetStrRef()) {
            CTStrData strData = ser.getCat().getStrRef().getStrCache();
            strData.getPtList().clear();
            for (int i = 2; i <= 10; i++) {
                CTStrVal strVal = strData.addNewPt();
                strVal.setIdx(i - 1);
                if (i == 5) {
                    continue;
                }
                String value = sheet.getRow(i).getCell(0).getStringCellValue();
                strVal.setV(value);
            }
            strData.setPtCount(CTUnsignedInt.Factory.newInstance());
            strData.getPtCount().setVal(8);
        }

        // 填充数据
        if (ser.isSetVal() && ser.getVal().isSetNumRef()) {
            CTNumData numData = ser.getVal().getNumRef().getNumCache();
            numData.getPtList().clear();

            // 重新填充数值
            // 行 1-4 是数据 -> 3 - 11
            for (int i = 2; i <= 10; i++) {
                CTNumVal numVal = numData.addNewPt();
                numVal.setIdx(i - 1);
                if (i == 5) {
                    continue;
                }
                String value = Double.toString(sheet.getRow(i).getCell(1).getNumericCellValue());
                numVal.setV(value);
            }
            numData.setPtCount(CTUnsignedInt.Factory.newInstance());
            numData.getPtCount().setVal(8);
        }
    }

    private static void updateSeriesData(CTLineSer ser, Sheet sheet) {
        if (ser.isSetCat() && ser.getCat().isSetStrRef()) {
            CTStrData strData = ser.getCat().getStrRef().getStrCache();
            strData.getPtList().clear();
            for (int i = 2; i <= 10; i++) {
                CTStrVal strVal = strData.addNewPt();
                strVal.setIdx(i - 1);
                if (i == 5) {
                    continue;
                }
                strVal.setV(sheet.getRow(i).getCell(0).getStringCellValue());
            }
            strData.setPtCount(CTUnsignedInt.Factory.newInstance());
            strData.getPtCount().setVal(8);
        }

        if (ser.isSetVal() && ser.getVal().isSetNumRef()) {
            CTNumData numData = ser.getVal().getNumRef().getNumCache();
            numData.getPtList().clear();
            for (int i = 2; i <= 10; i++) {
                CTNumVal numVal = numData.addNewPt();
                numVal.setIdx(i - 1);
                if (i == 5) {
                    continue;
                }
                Cell cell = sheet.getRow(i).getCell(2);
                numVal.setV(Double.toString(cell.getNumericCellValue()));
            }
            numData.setPtCount(CTUnsignedInt.Factory.newInstance());
            numData.getPtCount().setVal(8);
        }

    }


    private static void resetSheetData(XSSFSheet sheet, OOCChartData chartData) {
        List<OOCChartData.OOCData> oocDataList = chartData.getOocDataList();
        for (int i = 2; i < 11; i++) {
            if (i == 5) {
                continue;
            }
            XSSFRow row = sheet.getRow(i);
            OOCChartData.OOCData oocData = oocDataList.get(i - 2);
            row.getCell(0).setCellValue(oocData.getDimensionValue());
            row.getCell(1).setCellValue(oocData.getOocCount());
            row.getCell(2).setCellValue(oocData.getRatio());
        }
        log.info("数据填充完成");
        // 强制Excel工作簿重新计算公式，确保图表数据更新
        sheet.setForceFormulaRecalculation(true);
    }
}
